import mysql.connector
import re

def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            password=user_password,
            database=db_name
        )
        print("连接数据库成功")
    except mysql.connector.Error as error:
        print("连接")
        print("连接数据库失败，错误信息：", error)
        return None

    return connection


def read_txt_to_chapters(file_path):
    """ 读取小说文本文件并将其内容按章节存储到一个列表中 """
    chapters = []
    current_volume_title = ""
    current_chapter_title = ""
    current_chapter_content = ""
    in_chapter = False
    try:
        with open(file_path, 'r', encoding='gbk') as file:
            for line in file:
                line = line.strip()
                if line:
                    # 检查是否是卷标题
                    if re.search(r'第.*章', line):
                        if current_chapter_title and current_chapter_content:
                            chapters.append((current_volume_title, current_chapter_title, current_chapter_content))
                            current_chapter_content = ""
                        current_chapter_title = line
                        in_chapter = True

                    # 如果在章节中，累加内容
                    elif in_chapter:
                        if current_chapter_content:
                            current_chapter_content += " "
                        current_chapter_content += line

        # 添加最后一个章节
        if current_chapter_title and current_chapter_content:
            chapters.append((current_volume_title, current_chapter_title, current_chapter_content))

        print("成功读取小说文本文件并存储到章节列表中")
        return chapters
    except FileNotFoundError:
        print(f"文件未找到: {file_path}")
        return []
    except Exception as e:
        print(f"读取文件时出错: {e}")
        return []


def read_chapters_and_store_in_db(chapters, cursor):
    """ 遍历章节列表并将内容逐条插入到数据库中 """
    for volume_title, chapter_title, chapter_content in chapters:
        try:
            cursor.execute('INSERT INTO book_chapter (title,bookId, content) VALUES (%s,%s, %s)',
                           (chapter_title,'10010', chapter_content))
        except mysql.connector.Error as e:
            print(f"插入数据时出错: {chapter_title} - {e}")


def main():
    # 数据库连接信息
    host_name = "localhost"
    user_name = "root"
    user_password = "Dream2060#*@*#"
    db_name = "library"

    # 创建数据库连接
    db_connection = create_connection(host_name, user_name, user_password, db_name)
    if db_connection is None:
        return

    cursor = db_connection.cursor()

    # 读取小说文本文件并生成章节列表
    file_path = r'E:\123.txt'
    chapters = read_txt_to_chapters(file_path)

    # 遍历章节列表并打印出来
    for volume_title, chapter_title, chapter_content in chapters:
        print(f"卷名: {volume_title}, 章名: {chapter_title}, 内容: {chapter_content}")

    # 遍历章节列表并将内容保存到数据库
    read_chapters_and_store_in_db(chapters, cursor)

    # 提交更改并关闭连接
    db_connection.commit()
    db_connection.close()
    print("所有章节内容已成功保存到数据库")


if __name__ == "__main__":
    main()
